"""
版本说明：当前文件基于真格量化平台实现量化交易功能，参考原天勤量化程序，实现根据数据库参数计算开仓手数等功能。
"""

import time, os, sqlite3
from threading import Thread, Lock
from dbutils.pooled_db import PooledDB
import numpy as np
import pandas as pd
from rich.console import Console
from rich.theme import Theme
import logging

# 假设真格量化 SDK 名为 zgq ，实际使用时需替换为真实 SDK 名称
try:
    import zgq
    from zgq import Auth
except ImportError:
    raise ImportError("请安装真格量化 SDK")

# 使用数据库连接池来管理数据库操作
# db = PooledDB(
#     creator=sqlite3,
#     database='trade_monitor.db',
#     maxconnections=5,
#     mincached=2,
#     maxcached=3,
#     maxshared=3,
#     blocking=True
# )

# 配置日志记录
logging.basicConfig(
    filename='trading_log.log',
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

# 定义颜色主题
custom_theme = Theme({
    "info": "bright_cyan",
    "warning": "bright_yellow",
    "error": "bright_red",
    "success": "bright_green",
    "trade": "bold bright_magenta"
})
console = Console(theme=custom_theme)

######################################## 定义全局变量########################################
symbols = []
api = None
threads = []

# 定义开仓资金比例（占可用资金的百分比）
POSITION_FUND_RATIO = 0.2  # 20%，可以根据需要调整
# 定义最大风险比例（已用资金占账户权益的最大比例）
MAX_RISK_RATIO = 0.2  # 20%，已用资金不能超过账户权益的20%

######################################## 定义函数########################################
# 新增函数：获取 config 表所有配置项
def get_all_configs():
    """
    从 config 表中获取所有配置项，以字典形式返回
    :return: 包含所有配置项的字典
    """
    try:
        # 每次查询都创建新的连接，避免线程安全问题
        conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
        cursor = conn.cursor()
        cursor.execute("SELECT key, value FROM config")
        results = cursor.fetchall()
        conn.close()
        return dict(results)
    except Exception as e:
        console.print(f"从数据库获取所有配置项时出错: {e}", style="error")
        return {}

def get_symbols():
    try:
        # 每次查询都创建新的连接，避免线程安全问题
        conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
        cursor = conn.cursor()
        # 查询品种代码和 mtp 字段
        cursor.execute("SELECT exchange_code FROM products WHERE is_monitored=1")
        products = cursor.fetchall()
        conn.close()
        # 返回包含品种代码和 mtp 值的列表
        return [product[0] for product in products]
    except Exception as e:
        console.print(f"从数据库获取监控品种时出错: {e}", style="error")
        return []

# 新增函数：记录交易到数据库
def insert_trade_record(symbol, direction, offset, volume, price, reason):
    try:
        # 每次查询都创建新的连接，避免线程安全问题
        conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO trade_records (product_name, direction, offset, volume, price, reason, timestamp) "
            "VALUES (?, ?, ?, ?, ?, ?, ?)",
            (symbol, direction, offset, volume, price, reason, time.strftime('%Y-%m-%d %X'))
        )
        conn.commit()
        conn.close()
    except Exception as e:
        console.print(f"记录交易到数据库时出错: {e}", style="error")

def check_position_and_orders(api, symbol):
    """
    检查指定品种是否有未完成订单或已有持仓
    :param api: 真格量化 API 对象
    :param symbol: 交易品种
    :return: (bool, str) - (是否可以开仓, 原因)
    """
    try:
        # 获取配置信息
        configs = get_all_configs()
        math_open_lots = configs.get('math_open_lots', 'off').lower() == 'on'
        
        # 检查持仓情况，需根据真格量化 SDK 调整
        position = api.get_position(symbol)
        if not math_open_lots:
            if hasattr(position, 'long_pos') and position.long_pos > 0:
                return False, f"{symbol}已有{position.long_pos}手多仓持仓"
            if hasattr(position, 'short_pos') and position.short_pos > 0:
                return False, f"{symbol}已有{position.short_pos}手空仓持仓"
        
        # 检查未完成订单（仅检查开仓单），需根据真格量化 SDK 调整
        orders = api.get_orders()
        for order in orders:
            if order.symbol == symbol and order.status not in ('FINISHED', 'CANCELED') and order.offset == 'OPEN':
                return False, f"{symbol}有未完成开仓订单"
        
        return True, "无未完成订单，可以开仓"
    except Exception as e:
        logging.error(f"检查{symbol}持仓和订单时出错: {str(e)}", exc_info=True)
        return False, f"检查出错: {str(e)}"

def stop_loss(symbol):
    # 在每个线程中创建独立的真格量化连接
    api = zgq.API(auth=Auth("your_username", "your_password"))  # 需替换为真实账号
    while True:
        
        #########################查询数据库中设置信息##########################################################
        try:
            # 每次查询都创建新的连接，避免线程安全问题
            conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
            cursor = conn.cursor()
            cursor.execute("SELECT limit_stoploss,mtp,dks FROM products WHERE exchange_code=? AND is_monitored=1", (symbol,))
            result = cursor.fetchone()
            conn.close()
            if result is None:
                stop_loss_limit = 3000  # 限额止损
                mtp = 5  # 移动止盈止损周期
                dks = "双向"  # 开仓方向
            else:
                stop_loss_limit = int(result[0])  # 限额止损
                # 处理 mtp 数据，确保其为有效的整数
                try:
                    mtp = int(result[1])
                    # 限制 mtp 在合理范围内，例如 1 到 3600
                    mtp = max(1, min(mtp, 3600))
                except (ValueError, TypeError):
                    console.print(f"从数据库获取 {symbol} 的 mtp 值无效，使用默认值", style="warning")
                    mtp = 5
                dks = str(result[2])  # 开仓方向
        except Exception as e:
            console.print(f"从数据库获取 {symbol} 止损限额时出错: {e}", style="error")
            return
        
        #########################获取数据逻辑#######################################################################
        # 需根据真格量化 SDK 调整获取 k 线数据逻辑
        try:
            # 示例：获取 k 线数据
            bars = api.get_bars(symbol, count=mtp, frequency='1m')

            # 此处可添加根据 k 线数据计算指标的逻辑
            # 示例：简单的移动平均线计算
            close_prices = [bar.close for bar in bars]
            if len(close_prices) >= 2:
                prev_close = close_prices[-2]
                current_close = close_prices[-1]

                # 简单的开仓逻辑示例
                if current_close > prev_close:
                    can_open, reason = check_position_and_orders(api, symbol)
                    if can_open:
                        # 获取账户信息，需根据真格量化 SDK 调整
                        account = api.get_account()
                        available_funds = account.available_funds
                        
                        # 计算开仓手数
                        configs = get_all_configs()
                        math_open_lots = configs.get('math_open_lots', 'off').lower() == 'on'
                        if math_open_lots:
                            # 示例：根据可用资金和开仓比例计算开仓手数
                            # 需根据实际合约价值和保证金比例调整
                            contract_value = current_close * 10  # 假设合约乘数为 10
                            margin_ratio = 0.1  # 假设保证金比例为 10%
                            volume = int((available_funds * POSITION_FUND_RATIO) / (contract_value * margin_ratio))
                            volume = max(1, volume)  # 至少开 1 手
                        else:
                            volume = 1

                        # 下单开仓，需根据真格量化 SDK 调整
                        try:
                            order = api.place_order(
                                symbol=symbol,
                                direction='BUY',
                                offset='OPEN',
                                volume=volume,
                                price=current_close
                            )
                            insert_trade_record(symbol, 'BUY', 'OPEN', volume, current_close, '价格上涨开仓')
                            console.print(f"成功开仓 {symbol} {volume} 手", style="success")
                        except Exception as e:
                            console.print(f"开仓 {symbol} 失败: {e}", style="error")
                    else:
                        console.print(f"无法开仓 {symbol}: {reason}", style="warning")

                # 简单的止损逻辑示例
                position = api.get_position(symbol)
                if hasattr(position, 'long_pos') and position.long_pos > 0:
                    cost_price = position.cost_price
                    if current_close <= cost_price - stop_loss_limit:
                        try:
                            order = api.place_order(
                                symbol=symbol,
                                direction='SELL',
                                offset='CLOSE',
                                volume=position.long_pos,
                                price=current_close
                            )
                            insert_trade_record(symbol, 'SELL', 'CLOSE', position.long_pos, current_close, '触发止损')
                            console.print(f"成功止损 {symbol} {position.long_pos} 手", style="success")
                        except Exception as e:
                            console.print(f"止损 {symbol} 失败: {e}", style="error")

        except Exception as e:
            console.print(f"获取 {symbol} k 线数据时出错: {e}", style="error")

        # 控制循环频率
        time.sleep(1)

if __name__ == '__main__':
    # 初始化真格量化 API，需替换为真实账号
    api = zgq.API(auth=Auth("your_username", "your_password"))
    
    # 获取监控品种列表
    symbols = get_symbols()
    
    # 为每个品种创建一个止损线程
    for symbol in symbols:
        thread = Thread(target=stop_loss, args=(symbol,))
        thread.start()
        threads.append(thread)
    
    # 等待所有线程结束
    for thread in threads:
        thread.join()